package com.whzykj.ctrl;

import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.CellRangeAddress;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Record;
import com.whzykj.common.DateUtil;
import com.whzykj.model.AccountSeq;
import com.whzykj.model.Userinfo;

public class CheckAccountController  extends Controller{
	
	public void index() {
		this.renderJsp("/admin/system/acct/checkaccount.jsp");
	}
	
	/**
	 * 查询配送清单列表(新的)
	 */
	public void assignNewlist() {
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> nList=findAssignNewOrderList(startDate,endDate,stationid);
		this.renderJson(nList);
	}
	
	/**
	 * 导出财务报表到Excel
	 */
	public void exportAssignNewlist(){
		Userinfo userinfo = (Userinfo) this.getSessionAttr("LOGIN_USER");
		if (userinfo == null) {
			this.renderJsp("/login.jsp");
			return;
		}
		String startDate = this.getPara("startDate");
		String endDate = this.getPara("endDate");
		Integer stationid = this.getParaToInt("stationid");
		List<Record> nList=findAssignNewOrderList(startDate,endDate,stationid);
		if(nList!=null && nList.size()>0){
			for(Record rec:nList){
				rec.set("station", rec.getStr("station_name")+"\r\n"+rec.getStr("station_master"));
				if(rec.getStr("cust_name").equals("站点合计") || rec.getStr("cust_name").equals("汇总统计")){
					rec.set("customer", rec.getStr("cust_name"));
				}else{
					rec.set("customer", rec.getStr("cust_name")+"\r\n"+rec.getStr("mobile"));
				}
			}
		}
		
		String[] header = { "站点信息", "客户信息", "日期", "虚拟充值","线下充值","支付宝充值","微信充值","银联充值","活动充值","代金券充值","汇总" };
		String[] columns={"station","customer","create_time","00str","01str","02str","03str","04str","05str","06str","total"};
		 // 创建工作薄  (换行 /r/n)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();  
        // sheet:一张表的简称  
        // row:表里的行  
        // 创建工作薄中的工作表  
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("assignlist");
        HSSFCellStyle cellStyle = hssfWorkbook.createCellStyle(); 
        HSSFFont f=hssfWorkbook.createFont();
        f.setFontName("微软雅黑");
        f.setFontHeightInPoints((short)9);
        cellStyle.setFont(f);
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); 
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setWrapText(true);//先设置为自动换行
        // 用于格式化单元格的数据
//        HSSFDataFormat format = hssfWorkbook.createDataFormat();
        // 创建行  
        HSSFRow row = hssfSheet.createRow(0);
        row.setHeightInPoints(27);  
        // 创建单元格，设置表头 创建列  
        HSSFCell cell = null;  
        //便利表头  
        for (int i = 0; i < header.length; i++) {  
            //创建传入进来的表头的个数  
            cell = row.createCell(i);  
            //表头的值就是传入进来的值  
            cell.setCellValue(header[i]);
            cell.setCellStyle(cellStyle);	// 为单元格添加样式
        } 
        // 设置列宽
        hssfSheet.setColumnWidth(0, 20 * 256); 
        hssfSheet.setColumnWidth(1, 20 * 256);
        hssfSheet.setColumnWidth(2, 18 * 256); 
        hssfSheet.setColumnWidth(3, 13 * 256);
        hssfSheet.setColumnWidth(4, 13 * 256);
        hssfSheet.setColumnWidth(5, 13 * 256);
        hssfSheet.setColumnWidth(6, 13 * 256);
        hssfSheet.setColumnWidth(7, 13 * 256);
        hssfSheet.setColumnWidth(8, 13 * 256);
        hssfSheet.setColumnWidth(9, 13 * 256);
        hssfSheet.setColumnWidth(10, 13 * 256);
        hssfSheet.setColumnWidth(11, 13 * 256);
        
        try { 
        	int count=0;
            if(nList!=null && nList.size()>0){
            	for(Record rec:nList){
            		row = hssfSheet.createRow(++count);
            		row.setHeightInPoints(27); 
            		for (int i = 0; i < header.length; i++) {           			
            			cell=row.createCell(i);
            			cell.setCellStyle(cellStyle);	// 为单元格添加样式
            			cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
//            			if(i>2){
//            				try {
//    							cell.setCellType(HSSFCell.CELL_TYPE_NUMERIC);//
//    							BigDecimal val=rec.getBigDecimal(columns[i]);
//    							if(val==null){
//    								cell.setCellValue(0);
//    							}else{
//    								cell.setCellValue(val.doubleValue());
//    							}
//    						} catch (Exception e) {
//    							// TODO Auto-generated catch block
//    							e.printStackTrace();
//    						} 
//            			}else{
//            				cell.setCellValue(rec.get(columns[i])==null?"":rec.get(columns[i]).toString());
//            			}		
            		}
            	}
            }
            
         // 设置合并单元格
            if(nList!=null && nList.size()>1){
            	Record r1=nList.get(0);
            	int startIndex=1;	// 0+1
            	int endIndex=1;
            	for(int i=1,size=nList.size();i<size;i++){
            		Record r2=nList.get(i);
            		if(!r2.getStr("station").equals(r1.getStr("station"))){
            			endIndex=i;
            			hssfSheet.addMergedRegion(new CellRangeAddress(startIndex, endIndex, 0, 0));            			
            			startIndex=i+1;
            		}
            		r1=nList.get(i);
            	}
            }
              
            HttpServletRequest request=this.getRequest();
            HttpServletResponse response=this.getResponse();
//            String filename="order.xls"; 
            String filename="财务对账"+DateUtil.getDateTime("yyyyMMddHHmmss", new Date())+".xls";
            response.setContentType("application/vnd.ms-excel");   
            response.setHeader("Content-disposition", "attachment;filename=" + new String(filename.getBytes("gb2312"), "iso8859-1")); //设置文件头编码方式和文件名
            OutputStream ouputStream = response.getOutputStream();   
            hssfWorkbook.write(ouputStream);   
            ouputStream.flush();   
            ouputStream.close();  
        } catch (FileNotFoundException e) {  
            e.printStackTrace();  
        } catch (IOException e) {  
            e.printStackTrace();  

        }
	}
	
	

	private List<Record> findAssignNewOrderList(String startDate,
			String endDate, Integer stationid) {
		if (startDate == null || startDate.equals("")) {
			//startDate = DateUtil.getToday();
		}
		if (endDate == null || endDate.equals("")) {
			//endDate = DateUtil.getToday();
		}
		if (stationid == null) {
			stationid = -1;
		}
//		List<Record> list = AccountSeq.dao.getAssignNewList(startDate, endDate,
//				String.valueOf(stationid));
		List<Record> list=AccountSeq.dao.getFinanceCheckList(startDate, endDate, ""+stationid);
		
		return list;
	}

}
